from flask import Flask, render_template, redirect, url_for, request, flash, Markup
from flask_wtf import CSRFProtect, FlaskForm
from wtforms import StringField, SubmitField, SelectField

from flask_bootstrap import Bootstrap
from sqlalchemy import text, func, and_
# 数据库
from flask_sqlalchemy import SQLAlchemy
from wtforms.validators import DataRequired

import config

app = Flask(__name__)
app.config.from_object(config)
db = SQLAlchemy(app)
# 使用bootstrap4
bootstrap = Bootstrap(app)
csrf = CSRFProtect(app)
app.secret_key = 'secret'


# 企业基本信息建表模型
class Enterprise(db.Model):
    __tablename__ = 'yin_hang_enterprise_all'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    eid = db.Column(db.String(64))
    firm_name = db.Column(db.String(64))
    legal_rep = db.Column(db.String(200))
    est_date = db.Column(db.String(64))
    epm_num = db.Column(db.String(64))
    open_date = db.Column(db.String(64))
    bd_longitude = db.Column(db.String(64))
    bd_latitude = db.Column(db.String(64))
    reg_cap = db.Column(db.String(64))
    ope_scope = db.Column(db.String(2000))
    logo_url = db.Column(db.String(2000))
    # 立案指标
    case_one_year = db.Column(db.String(64))
    case_two_year = db.Column(db.String(64))
    case_three_year = db.Column(db.String(64))
    # 被执行指标
    execute_one_year = db.Column(db.String(64))
    execute_two_year = db.Column(db.String(64))
    execute_three_year = db.Column(db.String(64))
    # 裁判指标
    judge_one_year = db.Column(db.String(64))
    judge_two_year = db.Column(db.String(64))
    judge_three_year = db.Column(db.String(64))
    # 涉案金额指标
    one_all_money = db.Column(db.String(64))
    two_all_money = db.Column(db.String(64))
    three_all_money = db.Column(db.String(64))


# with app.app_context():
#     # 创建表
#     db.create_all()


@app.route('/')
def menhu():
    return render_template('menhu.html')


@app.route('/table')
def table():
    page = request.args.get('page', 1, type=int)
    pagination = Enterprise.query.paginate(page=page, per_page=20)
    messages = pagination.items
    titles = [('id', '#'), ('firm_name', '企业名称'), ('legal_rep', '法定代表人'), ('est_date', '注册日期'),
              ('case_one_year', '1年内立案次数'), ('case_two_year', '2年内立案次数'),
              ('case_three_year', '3年内立案次数'),
              ('execute_one_year', '1年内被执行次数'), ('execute_two_year', '2年内被执行次数'),
              ('execute_three_year', '3内被执行次数'),
              ('judge_one_year', '1年内裁判次数'), ('judge_two_year', '2年内裁判次数'),
              ('judge_three_year', '3年内裁判次数'),
              ('one_all_money', '1年内涉案金额'), ('two_all_money', '2年内涉案金额'),
              ('three_all_money', '3年内涉案金额')]
    return render_template('table.html', messages=messages, titles=titles, Enterprise=Enterprise, pagination=pagination)


@app.route('/table/<int:message_id>/view')
def view_message(message_id):
    message = Enterprise.query.get(message_id)
    ca = message.case_three_year
    ex = message.execute_three_year
    jd = message.judge_three_year
    if ca > 100 or ex > 100 or jd > 100:
        score = '案件次数过多，建议不放贷'
    else:
        score = 100 - (ca * 0.3 + ex * 0.3 + jd * 0.4)
    message.score = score
    title = [('id', '#'), ('epm_num', '员工人数'), ('reg_cap', '注册资本'), ('ope_scope', '企业涉及业务'),
             ('score', '评分')]
    return render_template('result.html', title=title, message=[message], Enterprise=Enterprise, ca=ca, ex=ex, jd=jd)


# 搜索表单
class GetForm(FlaskForm):
    name = StringField('', render_kw={'placeholder': '请输入公司名称'})
    submit = SubmitField('搜索')


# 搜索框
@csrf.exempt  # 对于部分不需要CSRF保护的使用exempt装饰器
@app.route('/search', methods=['post', 'get'])
def search():
    titles = [('id', '#'), ('firm_name', '企业名称'), ('legal_rep', '法定代表人'), ('est_date', '注册日期'),
              ('case_one_year', '1年内立案次数'), ('case_two_year', '2年内立案次数'),
              ('case_three_year', '3年内立案次数'),
              ('execute_one_year', '1年内被执行次数'), ('execute_two_year', '2年内被执行次数'),
              ('execute_three_year', '3内被执行次数'),
              ('judge_one_year', '1年内裁判次数'), ('judge_two_year', '2年内裁判次数'),
              ('judge_three_year', '3年内裁判次数'),
              ('one_all_money', '1年内涉案金额'), ('two_all_money', '2年内涉案金额'),
              ('three_all_money', '3年内涉案金额')]

    content = request.args.get('name')  # 需要查询的内容

    if content is None:
        content = " "
    page = request.args.get('page', 1, type=int)
    pagination = Enterprise.query.filter(Enterprise.firm_name.contains(content)).paginate(page=page,
                                                                                          per_page=int(18),
                                                                                          error_out=True)
    # 参数：error_out 设为True表示页数不是int或超过总页数时,会报错,并返回404状态码。 默认True

    messages = pagination.items
    return render_template('search.html', messages=messages, titles=titles, Enterprise=Enterprise,
                           pagination=pagination, form=GetForm())


# 筛选表单
class Zb(FlaskForm):
    case = SelectField('立案次数',
                       choices=[('case_one_year', '1年内立案次数'), ('case_two_year', '2年内立案次数'),
                                ('case_three_year', '3年内立案次数')])

    execute = SelectField('被执行次数',
                          choices=[('execute_one_year', '1年内被执行次数'), ('execute_two_year', '2年内被执行次数'),
                                   ('execute_three_year', '3年内被执行次数')])
    judge = SelectField('裁判次数',
                        choices=[('judge_one_year', '1年内裁判次数'), ('judge_two_year', '2年内裁判次数'),
                                 ('judge_three_year', '3年内裁判次数')])
    money = SelectField('总涉案金额',
                        choices=[('one_all_money', '1年内总涉案金额'), ('two_all_money', '2年内总涉案金额'),
                                 ('three_all_money', '3年内总涉案金额')])
    submit = SubmitField('搜索')


# 筛选框
@csrf.exempt  # 对于部分不需要CSRF保护的使用exempt装饰器
@app.route('/sift', methods=['post', 'get'])
def sift():
    page = request.args.get('page', 1, type=int)
    case = request.args.get('case')
    execute = request.args.get('execute')
    judge = request.args.get('judge')
    money = request.args.get('money')
    data = Enterprise.query
    titles = [('id', '#'), ('firm_name', '企业名称'), ('legal_rep', '法定代表人'), ('est_date', '注册日期'),
              ('case_one_year', '1年内立案次数'), ('case_two_year', '2年内立案次数'),
              ('case_three_year', '3年内立案次数'),
              ('execute_one_year', '1年内被执行次数'), ('execute_two_year', '2年内被执行次数'),
              ('execute_three_year', '3内被执行次数'),
              ('judge_one_year', '1年内裁判次数'), ('judge_two_year', '2年内裁判次数'),
              ('judge_three_year', '3年内裁判次数'),
              ('one_all_money', '1年内涉案金额'), ('two_all_money', '2年内涉案金额'),
              ('three_all_money', '3年内涉案金额')]
    if case is not None:  # 立案次数
        # 二次查询

        #     if case == 'case_one_year':
        #         case_pagination = Enterprise.query.filter(Enterprise.case_one_year > 0)
        #     elif case == 'case_two_year':
        #         case_pagination = Enterprise.query.filter(Enterprise.case_two_year > 0)
        #     else:
        #         case_pagination = Enterprise.query.filter(Enterprise.case_three_year > 0)
        #
        #     if execute == 'execute_one_year':
        #         execute_pagination = case_pagination.filter(Enterprise.execute_one_year > 0)
        #     elif execute == 'execute_two_year':
        #         execute_pagination = case_pagination.filter(Enterprise.execute_two_year > 0)
        #     else:
        #         execute_pagination = case_pagination.filter(Enterprise.execute_three_year > 0)
        #
        #     if judge == 'judge_one_year':
        #         judge_pagination = execute_pagination.filter(Enterprise.judge_one_year > 0)
        #     elif judge == 'judge_two_year':
        #         judge_pagination = execute_pagination.filter(Enterprise.judge_two_year > 0)
        #     else:
        #         judge_pagination = execute_pagination.filter(Enterprise.judge_three_year > 0)
        #
        #     if money == 'one_all_year':
        #         data = judge_pagination.filter(Enterprise.one_all_money > 0)
        #     elif money == 'two_all_year':
        #         data = judge_pagination.filter(Enterprise.judge_two_year > 0)
        #     else:
        #         data = judge_pagination.filter(Enterprise.judge_three_year > 0)

        # eval函数
        # data = Enterprise.query.filter(
        #     and_(eval(f'Enterprise.{case}>{0}'),
        #          eval(f'Enterprise.{execute}>{0}'),
        #          eval(f'Enterprise.{judge}>{0}'),
        #          eval(f'Enterprise.{money}>{0}')))

        # getattr函数
        data = Enterprise.query.filter(
            and_(getattr(Enterprise, case) > 0,
                 getattr(Enterprise, execute) > 0,
                 getattr(Enterprise, judge) > 0,
                 getattr(Enterprise, money) > 0))
        titles = [('id', '#'), ('firm_name', '企业名称'), ('legal_rep', '法定代表人'), ('est_date', '注册日期'),
                  (case, case.split('_')[1]+'年内立案次数'), (execute, execute.split('_')[1]+'年内被执行次数'),
                  (judge, judge.split('_')[1]+'年内裁判次数'),
                  (money, money.split('_')[0]+'年内涉案金额')]

    pagination = data.paginate(page=page, per_page=int(27), error_out=True)
    # 参数：error_out 设为True表示页数不是int或超过总页数时,会报错,并返回404状态码。 默认True
    messages = pagination.items
    return render_template('sift.html', messages=messages, titles=titles, Enterprise=Enterprise, pagination=pagination,
                           form=Zb())


# # 进入可视化页面
# @app.route('/judge_type')
# def judge_type():
#     return render_template('judge_type.html')
#
#
# @app.route('/map')
# def show_map():
#     return render_template("map.html")
#
#
# @app.route('/time_qushi')
# def time_qushi():
#     return render_template('time_qushi.html')
#
#
# @app.route('/superset')
# def superset():
#     return render_template('superset.html')


# # 测试数据库是否连接成功
# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("show tables"))  # sql语句要使用text函数进行格式转换
#         print(rs.fetchall())  # fetchall 若存在多条记录，则返回多个元组


if __name__ == '__main__':
    app.run()
